{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using Null\n",
    "\n",
    "- teacher\n",
    "\n",
    "id\t| dept\t| name\t| phone\t| mobile\n",
    "----|-------|-------|-------|-----\n",
    "101\t| 1 | Shrivell\t| 2753 | 07986 555 1234\n",
    "102\t| 1\t| Throd\t    | 2754 | 07122 555 1920\n",
    "103\t| 1\t| Splint\t| 2293\t|\n",
    "104 |\t| Spiregrain | 3287\t|\n",
    "105 | 2\t| Cutflower\t | 3212 | 07996 555 6574\n",
    "106 |\t| Deadyawn | 3345 |\t\n",
    "... |      |        |        |\n",
    "\n",
    "- dept\n",
    "\n",
    "id\t| name\n",
    "----|----\n",
    "1\t| Computing\n",
    "2\t| Design\n",
    "3\t| Engineering\n",
    "... |\n",
    "\n",
    "### Teachers and Departments\n",
    "The school includes many departments. Most teachers work exclusively for a single department. Some teachers have no department.\n",
    "\n",
    "[Selecting NULL values](https://sqlzoo.net/wiki/Selecting_NULL_values)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Prerequesites\n",
    "from pyhive import hive\n",
    "%load_ext sql\n",
    "%sql hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN\n",
    "\n",
    "List the teachers who have NULL for their department.\n",
    "\n",
    "> _Why we cannot use =_   \n",
    "> You might think that the phrase dept=NULL would work here but it doesn't - you can use the phrase dept IS NULL\n",
    "> \n",
    "> _That's not a proper explanation._  \n",
    "> No it's not, but you can read a better explanation at Wikipedia:NULL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spiregrain</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Deadyawn</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Spiregrain',), ('Deadyawn',)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name\n",
    "  FROM teacher\n",
    "    WHERE dept IS NULL"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Note the INNER JOIN misses the teachers with no department and the departments with no teacher."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>teacher</th>\n",
       "        <th>dept</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shrivell</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Throd</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Splint</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cutflower</td>\n",
       "        <td>Design</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shrivell', 'Computing'),\n",
       " ('Throd', 'Computing'),\n",
       " ('Splint', 'Computing'),\n",
       " ('Cutflower', 'Design')]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT teacher.name teacher, dept.name dept\n",
    " FROM teacher INNER JOIN dept\n",
    "    ON (teacher.dept=dept.id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "Use a different JOIN so that all teachers are listed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>teacher</th>\n",
       "        <th>dept</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shrivell</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Throd</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Splint</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spiregrain</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cutflower</td>\n",
       "        <td>Design</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Deadyawn</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shrivell', 'Computing'),\n",
       " ('Throd', 'Computing'),\n",
       " ('Splint', 'Computing'),\n",
       " ('Spiregrain', None),\n",
       " ('Cutflower', 'Design'),\n",
       " ('Deadyawn', None)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT teacher.name teacher, dept.name dept\n",
    "  FROM teacher LEFT JOIN dept\n",
    "    ON (teacher.dept=dept.id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "Use a different JOIN so that all departments are listed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>teacher</th>\n",
       "        <th>dept</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shrivell</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Throd</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Splint</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cutflower</td>\n",
       "        <td>Design</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>None</td>\n",
       "        <td>Engineering</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shrivell', 'Computing'),\n",
       " ('Throd', 'Computing'),\n",
       " ('Splint', 'Computing'),\n",
       " ('Cutflower', 'Design'),\n",
       " (None, 'Engineering')]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT teacher.name teacher, dept.name dept\n",
    "  FROM teacher RIGHT JOIN dept\n",
    "    ON (teacher.dept=dept.id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Using the [COALESCE](https://sqlzoo.net/wiki/COALESCE) function\n",
    "\n",
    "\n",
    "Use COALESCE to print the mobile number. Use the number '07986 444 2266' if there is no number given. **Show teacher name and mobile number or '07986 444 2266'**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>mobile</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shrivell</td>\n",
       "        <td>07986 555 1234</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Throd</td>\n",
       "        <td>07122 555 1920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Splint</td>\n",
       "        <td>07986 444 2266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spiregrain</td>\n",
       "        <td>07986 444 2266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cutflower</td>\n",
       "        <td>07996 555 6574</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Deadyawn</td>\n",
       "        <td>07986 444 2266</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shrivell', '07986 555 1234'),\n",
       " ('Throd', '07122 555 1920'),\n",
       " ('Splint', '07986 444 2266'),\n",
       " ('Spiregrain', '07986 444 2266'),\n",
       " ('Cutflower', '07996 555 6574'),\n",
       " ('Deadyawn', '07986 444 2266')]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, COALESCE(mobile, '07986 444 2266') mobile\n",
    "FROM teacher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>teacher</th>\n",
       "        <th>dept</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shrivell</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Throd</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Splint</td>\n",
       "        <td>Computing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spiregrain</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cutflower</td>\n",
       "        <td>Design</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Deadyawn</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shrivell', 'Computing'),\n",
       " ('Throd', 'Computing'),\n",
       " ('Splint', 'Computing'),\n",
       " ('Spiregrain', 'None'),\n",
       " ('Cutflower', 'Design'),\n",
       " ('Deadyawn', 'None')]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT teacher.name teacher, COALESCE(dept.name, 'None') dept\n",
    "FROM teacher LEFT JOIN dept ON (teacher.dept = dept.id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Use COUNT to show the number of teachers and the number of mobile phones."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>n_teacher</th>\n",
       "        <th>n_mobile</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>6</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[(6, 3)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT COUNT(name) n_teacher, COUNT(mobile) n_mobile\n",
    "FROM teacher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Use COUNT and GROUP BY **dept.name** to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>dept</th>\n",
       "        <th>n_teacher</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Computing</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Design</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Engineering</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Computing', 3), ('Design', 1), ('Engineering', 0)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT dept.name dept, COUNT(teacher.id) n_teacher\n",
    "FROM teacher RIGHT JOIN dept ON (teacher.dept=dept.id)\n",
    "GROUP BY dept.name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Using [CASE](https://sqlzoo.net/wiki/CASE)\n",
    "\n",
    "\n",
    "Use CASE to show the **name** of each teacher followed by 'Sci' if the teacher is in **dept** 1 or 2 and 'Art' otherwise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>flg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shrivell</td>\n",
       "        <td>Sci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Throd</td>\n",
       "        <td>Sci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Splint</td>\n",
       "        <td>Sci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spiregrain</td>\n",
       "        <td>Art</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cutflower</td>\n",
       "        <td>Sci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Deadyawn</td>\n",
       "        <td>Art</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shrivell', 'Sci'),\n",
       " ('Throd', 'Sci'),\n",
       " ('Splint', 'Sci'),\n",
       " ('Spiregrain', 'Art'),\n",
       " ('Cutflower', 'Sci'),\n",
       " ('Deadyawn', 'Art')]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, CASE WHEN dept IN (1, 2) THEN 'Sci'\n",
    "                                              ELSE 'Art'\n",
    "                                         END flg\n",
    "FROM teacher"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * hive://cloudera@quickstart.cloudera:10000/sqlzoo\n",
      "Done.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>flg</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Shrivell</td>\n",
       "        <td>Sci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Throd</td>\n",
       "        <td>Sci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Splint</td>\n",
       "        <td>Sci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spiregrain</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Cutflower</td>\n",
       "        <td>Sci</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Deadyawn</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Shrivell', 'Sci'),\n",
       " ('Throd', 'Sci'),\n",
       " ('Splint', 'Sci'),\n",
       " ('Spiregrain', 'None'),\n",
       " ('Cutflower', 'Sci'),\n",
       " ('Deadyawn', 'None')]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, CASE WHEN dept IN (1, 2) THEN 'Sci'\n",
    "                                           WHEN dept IN (3) THEN 'Art'\n",
    "                                           ELSE 'None'\n",
    "                               END flg\n",
    "FROM teacher"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
